package com.scs.application.modules.sys.controller;

import com.scs.application.core.utils.StringUtils;
import com.scs.application.modules.sys.dto.TableColumnDTO;
import com.scs.application.modules.sys.dto.TableInfoDTO;
import com.scs.application.modules.sys.model.RestResult;
import com.scs.application.modules.sys.utils.UtilDatabase;
import com.scs.application.modules.upgrade.db.UpgradeDatabaseService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/sys/database")
public class DatabaseController {

    @Autowired
    private JdbcTemplate jdbcTemplateDefault;

    @Autowired
    private UpgradeDatabaseService upgradeDatabaseService;

    //获取数据数据删除备份库的所有表
    @RequestMapping(value="/getDvdeleteDbTables", method= RequestMethod.GET)
    public RestResult getDvdeleteDbTables(){
        return RestResult.ok("获取成功", UtilDatabase.dbBackupTableColumnsDTOMap);
    }


    //获取数据数据删除备份库的某个表的所有数据
    @PostMapping(value="/getDvdeleteTableData")
    public RestResult getDvdeleteTableData(@RequestBody Map<String, String> reqData){
        String tableName=reqData.get("tableName"), deleteBegin = reqData.get("deleteBegin"),  deleteEnd=reqData.get("deleteEnd"),  searchKey=reqData.get("searchKey");
        String sql = "select * from %s where delete_date between '%s' and '%s' ";
        sql = String.format(sql,tableName,deleteBegin,deleteEnd);
        if (StringUtils.isNotBlank(searchKey)) {
            List<TableColumnDTO> tableColumnDTOS =  UtilDatabase.dbBackupTableColumnsDTOMap.get(tableName);
            if (tableColumnDTOS != null && tableColumnDTOS.size() > 0){
                List<String> sqlLike = new ArrayList<String>();
                tableColumnDTOS.stream().forEach(tableColumnDTO -> {
                    if (!tableColumnDTO.getDatatype().equals("date") && !tableColumnDTO.getDatatype().equals("datetime")) {
                        sqlLike.add(" "+tableColumnDTO.getColumnname()+" like '%"+searchKey+"%'");
                    }
                });
                if (sqlLike.size() > 0) sql+= " and ("+StringUtils.join(sqlLike,"or")+")";
            }
        }
        return RestResult.ok("获取成功", UtilDatabase.jdbcTemplateBackup.queryForList(sql));
    }

    /**
    * @Description：获取表信息列表
    */
    @GetMapping(value="/getTableInfo")
    public RestResult getTableInfo(){
        StringBuffer sbSql = new StringBuffer();
        sbSql.append(" select ")
                .append(" table_name as tableName,table_comment as tableComment,create_time as createTime,update_time as updateTime,substring_index(table_comment,'-',1) as class1 ")
                .append(" from INFORMATION_SCHEMA.Tables ")
                .append(" where  table_schema=(select database()) and table_name not like '%dvdelete%' and table_name not like '%view_%' and table_name not like '%report_temp%' and table_name not like '%fsd_%' and table_name not like '%_backup%' and table_comment  like '%-%' ")
                .append(" order by table_name asc ");
        return RestResult.ok("获取成功", jdbcTemplateDefault.query(sbSql.toString(), new BeanPropertyRowMapper<TableInfoDTO>(TableInfoDTO.class)));
    }
}
